﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using BolsaTrabajo.BusinessEntities;

namespace BolsaTrabajo.DataAccess
{
    public class UsersInRolesData
    {
        private String stConnection = String.Empty;

        public UsersInRolesData()
        {
            stConnection = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString();
        }

        public bool Registrar(UsersInRoles item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_UsersInRoles_Registrar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@userID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.UserId);
            SQLHelper.AddParam(ref cmd, "@roleID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.RoleId);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public bool Eliminar(UsersInRoles item)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_UsersInRoles_Eliminar";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@userID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.UserId);
            SQLHelper.AddParam(ref cmd, "@roleID", ParameterDirection.Input, SqlDbType.UniqueIdentifier, item.RoleId);

            bool resultado = false;

            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
                resultado = true;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                {
                    cn.Close();
                }
                cn.Dispose();
            }
            return resultado;
        }

        public List<UsersInRoles> ListarUsersInRolesPorUserName(String username)
        {
            SqlConnection cn = new SqlConnection(stConnection);
            SqlCommand cmd = cn.CreateCommand();
            cmd.CommandTimeout = 216000000;
            cmd.CommandText = "usp_UsersInRoles_ListarPorUserName";
            cmd.CommandType = CommandType.StoredProcedure;

            SQLHelper.AddParam(ref cmd, "@username", ParameterDirection.Input, SqlDbType.VarChar,256, username);

            List<UsersInRoles> lista = new List<UsersInRoles>();
            try
            {
                cn.Open();
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (dr.Read())
                {
                    UsersInRoles item = new UsersInRoles();

                    item.UserId = dr.GetGuid(dr.GetOrdinal("UserId"));
                    item.RoleId = dr.GetGuid(dr.GetOrdinal("RoleId"));

                    lista.Add(item);

                    item = null;
                }
                dr.Close();
            }
            catch (Exception)
            {

                throw;
            }
            finally
            {
                if (cn.State == ConnectionState.Open)
                    cn.Close();
                cn.Dispose();
            }
            return lista;

        }
    }
}
